﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data;
using System.Data.SqlClient;
namespace DA
{
    public class MyQueryDA : DBConnection
    {
        public MyQueryDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
        public int getId(string query)
        {
            var id = 0;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return 0;
                }
                while (sqlDataReader.Read())
                {
                    id = sqlDataReader.GetInt32(0);
                }
                close();
                return id;
            }
            catch
            {
                return 0;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }

        public int getSumList(string query)
        {
            var id = 0;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return 0;
                }
                while (sqlDataReader.Read())
                {
                    id += sqlDataReader.GetInt32(0);
                }
                close();
                return id;
            }
            catch
            {
                return 0;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }

        public List<int> getListId(string query)
        {
            var list = new List<int>();
            var id = 0;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return null;
                }
                while (sqlDataReader.Read())
                {
                    id = sqlDataReader.GetInt32(0);
                    list.Add(id);
                }
                close();
                return list;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
            return null;
        }

        public int getCountList(string query)
        {
            var id = 0;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return 0;
                }
                while (sqlDataReader.Read())
                {
                    id++;
                }
                close();
                return id;
            }
            catch
            {
                return 0;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }

        public string getValues(string query)
        {
            var values = string.Empty;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return string.Empty;
                }
                while (sqlDataReader.Read())
                {
                    values = sqlDataReader[0].ToString();
                }
                close();
                return values;
            }
            catch
            {
                return values;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }

        public string[] getArrValues(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                var mang = new string[sqlDataReader.FieldCount];
                if (sqlDataReader == null)
                {
                    return null;
                }
                while (sqlDataReader.Read())
                {
                    for (var i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        mang[i] = sqlDataReader[i].ToString();
                    }
                }
                close();
                return mang;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }
        public DataSet doQuery(string query)
        {
            DataSet ds = null;
            ds = new DataSet();
            try
            {
                open();
                sqlCommand = new SqlCommand(query, sqlConnection);
                var adapter = new SqlDataAdapter();
                adapter.SelectCommand = sqlCommand;
                adapter.Fill(ds);
                close();
                return ds;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
        }

        public bool doNonQuery(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        
    }
}
